package mya.mes.mdc.model.excel;


import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.util.List;


/**
 * 报警excel解析
 */
public class ExcelParse {


  boolean processFormula = false;

  DataFormatter formatter = new DataFormatter();

  FormulaEvaluator evaluator;

  public static void main(String[] args) {
    // Use a file
    try {

      ExcelParse parse = new ExcelParse();
      String EXCEL_FILE_NAME = "D:\\mdc\\excel\\CUR\\BD4.xlsx";
      ExcelData data = parse.parse(EXCEL_FILE_NAME);

      System.out.println(data.tagDefList.size() + " ," + data.tagAlarmDefList.size());

    } catch (IOException e) {
      e.printStackTrace();
    }
  }

  public ExcelData parse(String excelFile) throws IOException {
    ExcelData excelData = new ExcelData();
    Workbook wb = null;
    try {
      wb = WorkbookFactory.create(new File(excelFile));
      evaluator = wb.getCreationHelper().createFormulaEvaluator();
      Sheet sheet0 = wb.getSheetAt(0);
      Sheet sheet1 = wb.getSheetAt(1);
      readSheet0(sheet0, excelData.tagDefList);
      readSheet1(sheet1, excelData.tagAlarmDefList);
      wb.close();
      wb = null;

    } finally {
      if (wb != null) {
        wb.close();
      }
    }
    return excelData;
  }

  private void readSheet0(Sheet sheet, List<ExcelTagDef> list) throws IOException {


    for (Row row : sheet) {
      if (row.getRowNum() < 1) {
        //忽略表格的标题行
        continue;
      }
      ExcelTagDef tagDef = new ExcelTagDef();

      tagDef.rowNum = row.getRowNum();

      tagDef.tagName = getString(row, 0);
      tagDef.plcAddress = getString(row, 1);
      tagDef.dataType = getString(row, 2);
      tagDef.respectDataType = getString(row, 3);
      tagDef.clientAddress = getString(row, 4);
      tagDef.scanRate = getString(row, 5);
      tagDef.scaling = getString(row, 6);

      tagDef.rawLow = getString(row, 7);
      tagDef.rawHigh = getString(row, 8);

      tagDef.scaledLow = getString(row, 9);
      tagDef.scaledHigh = getString(row, 10);

      tagDef.scaledDataType = getString(row, 11);

      tagDef.clampLow = getString(row, 12);
      tagDef.clampHigh = getString(row, 13);

      tagDef.engUnits = getString(row, 14);
      tagDef.description = getString(row, 15);

      tagDef.negateValue = getString(row, 16);


      list.add(tagDef);
    }
  }

  private void readSheet1(Sheet sheet, List<ExcelTagAlarmDef> list) throws IOException {
    for (Row row : sheet) {

      if (row.getRowNum() < 1) {
        //忽略表格的标题行
        continue;
      }

      ExcelTagAlarmDef alertDef = new ExcelTagAlarmDef();

      alertDef.rowNum = row.getRowNum();

      alertDef.plcAddress = getString(row, 0);
      alertDef.no = getString(row, 1);
      alertDef.index = getInteger(row, 2);
      alertDef.bit = getInteger(row, 3);
      alertDef.rdbTag = getString(row, 4);
      alertDef.ref = getString(row, 5);
      alertDef.enUs = getString(row, 6);
      alertDef.zhCN = getString(row, 7);
      alertDef.needSendToManager = getInteger(row,8) == 1 ? true : false ;
      alertDef.needSendToPerson = getInteger(row,9) == 1 ? true : false ;
      alertDef.msgNotice = getString(row,8) != null ? false : true;

      if ("0".equals(alertDef.rdbTag)) {
        continue;
      }
//      if(isEmpty(alertDef.plcAddress) && isEmpty(alertDef.rdbTag)) {
//        continue;
//      }
      list.add(alertDef);
    }
  }

  private boolean isEmpty(String str) {
    if (str == null || str.isEmpty()) {
      return true;
    }

    str = str.trim();

    return str.isEmpty();
  }

  private String getString(Row row, int cellIndex) {
    Cell cell = row.getCell(cellIndex);
    if (cell == null) {
      return null;
    }

    CellType cellType = cell.getCellType();

    if (CellType.BLANK.equals(cellType)) {
      return null;
    }


    if (CellType.FORMULA.equals(cellType)) {

      if (!processFormula) {
        return null;
      }
      CellValue cellValue = evaluator.evaluate(cell);

      CellType cellType1 = cellValue.getCellType();


      if (CellType.STRING.equals(cellType1)) {
        return cellValue.getStringValue();
      } else if (CellType.ERROR.equals(cellType1)) {
        return null;
      } else if (CellType.BLANK.equals(cellType1)) {
        return null;
      } else {
        return cellValue.formatAsString();
      }

    }


    String text = formatter.formatCellValue(cell);

    if (text != null) {
      text = text.trim();
    }
    return text;
  }

  private Integer getInteger(Row row, int cellIndex) {
    Cell cell = row.getCell(cellIndex);
    if (cell == null) {
      return null;
    }

    CellType cellType = cell.getCellType();

    if (CellType.BLANK.equals(cellType) || CellType.ERROR.equals(cellType)) {
      return null;
    }


    if (CellType.FORMULA.equals(cellType)) {
      if (!processFormula) {
        return null;
      }
      CellValue cellValue = evaluator.evaluate(cell);

      CellType cellType1 = cellValue.getCellType();

      if (CellType.BLANK.equals(cellType1) || CellType.ERROR.equals(cellType1)) {
        return null;
      }

      if (CellType.NUMERIC.equals(cellType1)) {
        return new Double(cellValue.getNumberValue()).intValue();
      }


      if (CellType.STRING.equals(cellType1)) {
        String text = cellValue.getStringValue();

        return getAsInt(text);
      }

      return null;

//      if (CellType.NUMERIC.equals(cellType1)) {
//        return cellValue.getStringValue();
//      } else if (CellType.ERROR.equals(cellType1)) {
//        return null;
//      } else if (CellType.BLANK.equals(cellType1)) {
//        return null;
//      } else {
//        return cellValue.formatAsString();
//      }

    }


    if (CellType.NUMERIC.equals(cellType)) {

      return new Double(cell.getNumericCellValue()).intValue();
    } else if (CellType.STRING.equals(cellType)) {

      String text = formatter.formatCellValue(cell);
      return getAsInt(text);

    } else if (CellType.FORMULA.equals(cellType)) {
      cell.getCellFormula();
    }

    return null;
  }

  private Integer getAsInt(String text) {
    if (text != null) {
      text = text.trim();
    }

    if (text == null || text.isEmpty()) {
      return null;
    }

    try {
      return Integer.parseInt(text);
    } catch (Exception e) {
      return null;
    }

  }
}
